<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
*/

/**
 * Description of model_transaksi
 *
 * @author Hadi
 */
class model_transaksi extends Default_My_Model {

    function __construct() {
        parent::__construct();
    }

    function getCustomerList() {
        return $this->db->query("SELECT id_customer,nama_customer FROM master_customer");
    }

    function getTermConditionList() {
        return $this->db->query("SELECT id_tc, nama_tc, isi_tc FROM master_term_condition");
    }

    public function getTC($kode) {
        return $this->db->query("SELECT id_tc, isi_tc FROM master_term_condition WHERE id_tc = '$kode'" );
    }

    public function getPrefixQuotationNo() {
        $last_id =  $this->get_scalar("SELECT max(id_quotation) as id FROM data_quotation_to_cust ", "id");
        $number = $last_id+1;
        $prefix = str_pad($number, 5,0,STR_PAD_LEFT);
        return $prefix;
    }

    public function getPrefixDeliveryNo() {
        $last_id =  $this->get_scalar("SELECT max(id_dn) as id FROM data_dn_to_cust", "id");
        $number = $last_id+1;
        $prefix = str_pad($number, 5,0,STR_PAD_LEFT);
        return $prefix;
    }

    public function getPrefixInvoiceNo() {
        $last_id =  $this->get_scalar("SELECT max(id_invoice) as id FROM data_invoice_to_cust", "id");
        $number = $last_id+1;
        $prefix = str_pad($number, 5,0,STR_PAD_LEFT);
        return $prefix;
    }

    public function getPrefixPurchaseNo() {
        $last_id =  $this->get_scalar("SELECT max(id_po) as id FROM data_po_from_customer", "id");
        $number = $last_id+1;
        $prefix = str_pad($number, 5,0,STR_PAD_LEFT);
        return $prefix;
    }

    function getQuotationData($whr, $offset = 0, $length= 30) {
        $sql = "SELECT q.*, c.*, v.nama_vessel, p.nama_port, t.isi_tc
                FROM data_quotation_to_cust q
                LEFT JOIN master_customer c ON q.id_customer = c.id_customer
                LEFT JOIN master_vessel v ON q.id_vessel = v.id_vessel 
                LEFT JOIN master_port p ON p.id_port = q.id_port
                LEFT JOIN master_term_condition t ON t.id_tc = q.id_tc
                $whr LIMIT $length OFFSET $offset";

        return $this->db->query($sql);
    }

    function deleteQuotation($kode) {
        $this->db->delete("data_quotation_to_cust", "id_quotation = '$kode'");
    }

    function getPurchaseData($whr, $offset = 0, $length= 30) {
        $sql = "SELECT p.*, c.*
                FROM data_po_from_customer p
                LEFT JOIN master_customer c ON p.id_customer = c.id_customer
                $whr LIMIT $length OFFSET $offset";

        return $this->db->query($sql);
    }

    function deletePurchase($kode) {
        $this->db->delete("data_po_from_customer", "id_po = '$kode'");
    }

    function getInvoiceData($whr, $offset = 0, $length= 30) {
        $sql = "SELECT i.*, c.*
                FROM data_invoice_to_cust i
                LEFT JOIN data_po_from_customer p ON i.id_po = p.id_po
                LEFT JOIN master_customer c ON p.id_customer = c.id_customer
                $whr LIMIT $length OFFSET $offset";
        return $this->db->query($sql);
    }

    function deleteInvoice($kode) {
        $this->db->delete("data_invoice_to_cust", "id_invoice = '$kode'");
    }

    function insertCustomer($data) {
        $id = $this->namaPelangganExist($data["nama_customer"]);
        if ($id == "") {
            $kontak = explode("/", $data["kontak_komplit"]);
            $alamat = explode("\n", $data["alamat_komplit"]);
            $set = array(
                    "nama_customer" => $data["nama_customer"],
                    "alamat_detail_customer" => $data["alamat_detail_customer"],
                    "attendance" => $data["attendance"] ? $data["attendance"] : "NULL",
                    "telepon_customer" => $kontak[0],
                    "fax_customer" => $kontak[1],
                    "email_customer" => $kontak[2],
                    "alamat_customer" => $alamat[0],
                    "kota_customer" => $alamat[1],
                    "kode_pos_customer" => $alamat[2]
            );
            $this->db->insert("master_customer", $set);
            return $this->db->insert_id();
        }
        return $id;
    }

    function insertQuotation($data) {
        $set = array(
                "tanggal_quotation" => $data["tanggal_quotation"],
                "nomor_quotation" => $data["nomor_quotation"],
                "rf_number" => $data["rf_number"],
                "id_customer" => $data["id_customer"],
                "id_vessel" => $data["id_vessel"],
                "id_port" => $data["id_port"],
                "id_tc" => $data["termcondition"],
                "using_dollar_currency" => $data["using_dollar_currency"],
                "approved_by" => $data["approved_by"],
                "quote_by" => $data["quote_by"],
                "date_submit" => $data["date_submit"],
                "subject" => $data["subject"],
                "submitter" => $data["submitter"],
                "using_ppn" => $data["ppn"],
                "diskon" => $data["diskon"]
        );
        $this->db->insert("data_quotation_to_cust", $set);
        return $this->db->insert_id();
    }

    function updateQuotation($data, $id_quot) {
        $set = array(
                "tanggal_quotation" => $data["tanggal_quotation"],
                "nomor_quotation" => $data["nomor_quotation"],
                "rf_number" => $data["rf_number"],
                "id_customer" => $data["id_customer"],
                "id_vessel" => $data["id_vessel"],
                "id_port" => $data["id_port"],
                "id_tc" => $data["termcondition"],
                "using_dollar_currency" => $data["using_dollar_currency"],
                "approved_by" => $data["approved_by"],
                "quote_by" => $data["quote_by"],
                "date_submit" => $data["date_submit"],
                "subject" => $data["subject"],
                "submitter" => $data["submitter"],
                "using_ppn" => $data["ppn"],
                "diskon" => $data["diskon"]
        );
        $this->db->update("data_quotation_to_cust", $set, "id_quotation = '$id_quot'");
    }

    function insertVessel($data) {
        $set = array(
                "nama_vessel" => $data["nama_vessel"]
        );
        $this->db->insert("master_vessel", $set);
        return $this->db->insert_id();
    }

    function insertPort($data) {
        $set = array(
                "nama_port" => $data["nama_port"]
        );
        $this->db->insert("master_port", $set);
        return $this->db->insert_id();
    }

    function getItem($id) {
        return $this->get_object("SELECT * FROM master_item WHERE id_item  = '$id'");
    }

    function insertQuotationItem($set) {
        $this->db->insert("data_quotation_to_cust_item", $set);
    }

    function getQuotationItem($id) {
        return $this->db->query("SELECT * FROM data_quotation_to_cust_item WHERE id_quotation = '$id'");
    }

    function getDeliveryItem($id) {
        return $this->db->query("SELECT * FROM data_dn_to_cust_item WHERE id_dn = '$id'");
    }

    function getPOItem($id) {
        return $this->db->query("SELECT * FROM data_po_from_customer_item WHERE id_po = '$id'");
    }

    function getInvoiceItem($id) {
        return $this->db->query("SELECT * FROM data_invoice_to_cust_item WHERE id_invoice = '$id'");
    }

    function getInvoiceBank($crc) {
        return $this->db->query("SELECT nama_bank, cabang, nomor_rekening FROM master_bank WHERE use_dollar = ".($crc ? "1" : "0"));
    }

    function insertDelivery($data, $id_invoice = "") {
        $set = array(
                "tanggal_dn" => $data["tanggal_dn"],
                "nomor_dn" => $data["nomor_dn"],
                "to" => $data["to"],
                "rf_number" => $data["rf_number"],
                "delivery_to" => $data["delivery_to"],
                "location" => $data["location"],
                "subject" => $data["subject"],
                "submitter" => $data["submitter"],
                "date_submit" => $data["date_submit"],
                "checked_by" => $data["checked_by"],
                "delivered_by" => $data["delivered_by"],
                "received_by" => $data["received_by"],
                "received_by_2" => $data["received_by_2"]
        );
        if($id_invoice != "")
            $set["id_invoice"] = $id_invoice;
        $this->db->insert("data_dn_to_cust", $set);
        return $this->db->insert_id();
    }

    function updateDelivery($data, $id_dn, $id_invoice = "") {
        $set = array(
                "tanggal_dn" => $data["tanggal_dn"],
                "nomor_dn" => $data["nomor_dn"],
                "to" => $data["to"],
                "rf_number" => $data["rf_number"],
                "delivery_to" => $data["delivery_to"],
                "location" => $data["location"],
                "subject" => $data["subject"],
                "submitter" => $data["submitter"],
                "date_submit" => $data["date_submit"],
                "checked_by" => $data["checked_by"],
                "delivered_by" => $data["delivered_by"],
                "received_by" => $data["received_by"],
                "received_by_2" => $data["received_by_2"]
        );
        if($id_invoice != "")
            $set["id_invoice"] = $id_invoice;
        $this->db->update("data_dn_to_cust", $set, "id_dn = '$id_dn'");
    }

    function insertDeliveryItem($id_dn, $id_item, $qty) {
        $data_item = $this->getItem($id_item);
        $set = array(
                "id_dn" => $id_dn,
                "id_item" => $id_item,
                "nama_item" => $data_item->nama_item,
                "satuan_beli" => $data_item->satuan_beli,
                "quantity" => $qty
        );
        $this->db->insert("data_dn_to_cust_item", $set);
    }

    function getDeliveryData($whr, $offset = 0, $length= 30) {
        $sql = "SELECT d.*, i.id_invoice
                FROM data_dn_to_cust d
                LEFT JOIN data_invoice_to_cust i ON d.id_invoice = i.id_invoice
                $whr LIMIT $length OFFSET $offset";

        return $this->db->query($sql);
    }

    function deleteDelivery($kode) {
        $this->db->delete("data_dn_to_cust", "id_dn = '$kode'");
    }

    function insertInvoice($data) {
        $set = array(
                "id_invoice" => $data["temp_id_iv"],
                "tanggal_invoice" => $data["tanggal_invoice"],
                "nomor_invoice" => $data["nomor_invoice"],
                "id_po" => $data["id_po"],
                "using_dollar_currency" => $data["using_dollar_currency"],
                "list_dn_id" => $data["list_dn_id"],
                "tambahkan_ppn" => $data["ppn"],
                "penerbit_invoice" => $data["penerbit_invoice"],
                "submitter" => $data["submitter"],
                "date_submit" => $data["date_submit"],
                "diskon" => $data["diskon_iv"]
        );
        $this->db->insert("data_invoice_to_cust", $set);
        return $this->db->insert_id();
    }

    function updateInvoice($data, $id_invoice) {
        $set = array(
                "id_invoice" => $data["temp_id_iv"],
                "tanggal_invoice" => $data["tanggal_invoice"],
                "nomor_invoice" => $data["nomor_invoice"],
                "id_po" => $data["id_po"],
                "using_dollar_currency" => $data["using_dollar_currency"],
                "list_dn_id" => $data["list_dn_id"],
                "tambahkan_ppn" => $data["ppn"],
                "penerbit_invoice" => $data["penerbit_invoice"],
                "submitter" => $data["submitter"],
                "date_submit" => $data["date_submit"],
                "diskon" => $data["diskon_iv"]
        );
        $this->db->update("data_invoice_to_cust", $set, "id_invoice = '$id_invoice'");
    }

    function insertInvoiceItem($id_invoice, $row) {        
        $set = array(
                "id_invoice" => $id_invoice,
                "id_item" => $row["id_item"],
                "nama_item" => $row["nama_item"],
                "satuan_beli" => $row["satuan_beli"],
                "quantity" => $row["quantity"],
                "harga_beli" => $row["harga_beli"],
                "harga_jual" => $row["harga_jual"],
                "harga_beli_dollar" => $row["harga_beli_dollar"],
                "harga_jual_dollar" => $row["harga_jual_dollar"]
        );
        $this->db->insert("data_invoice_to_cust_item", $set);       
    }

    function insertPurchase($data) {
        $set = array(
                "tanggal_po" => $data["tanggal_po"],
                "nomor_po" => $data["nomor_po"],
                "list_quotation_id" => $data["list_quotation_id"],
                "id_customer" => $data["id_customer"],
                "using_dollar_currency" => $data["using_dollar_currency"],
                "catatan_po" => $data["catatan_po"],
                "date_submit" => $data["date_submit"],
                "submitter" => $data["submitter"],
                "diskon" => $data["diskon"]
        );
        if(!empty($data["file_upload"]))
            $set["file_attachment_po"] = $data["file_upload"];

        $this->db->insert("data_po_from_customer", $set);
        return $this->db->insert_id();
    }

    function updatePurchase($data, $id_po) {
        $set = array(
                "tanggal_po" => $data["tanggal_po"],
                "nomor_po" => $data["nomor_po"],
                "list_quotation_id" => $data["list_quotation_id"],
                "id_customer" => $data["id_customer"],
                "using_dollar_currency" => $data["using_dollar_currency"],
                "catatan_po" => $data["catatan_po"],
                "date_submit" => $data["date_submit"],
                "submitter" => $data["submitter"],
                "diskon" => $data["diskon"]
        );
        if(!empty($data["file_upload"]))
            $set["file_attachment_po"] = $data["file_upload"];
        $this->db->update("data_po_from_customer", $set, "id_po = '$id_po'");
    }

    function insertPurchaseItem($id_po, $key, $data) {        
        $set = array(
                "id_po" => $id_po,
                "id_item" => $data["id_item"][$key],
                "nama_item" => $data["nama_item"][$key],
                "satuan_beli" => $data["satuan_beli"][$key],
                "quantity" => $data["quantity"][$key],
                "harga_beli" => $data["harga_beli"][$key],
                "harga_jual" => $data["harga_jual"][$key],
                "harga_beli_dollar" => $data["harga_beli_dollar"][$key],
                "harga_jual_dollar" => $data["harga_jual_dollar"][$key]
        );        
        $this->db->insert("data_po_from_customer_item", $set);
    }

    function nextInvoiceVal() {
        $ai = $this->get_scalar("SELECT AUTO_INCREMENT FROM information_schema.tables
                                 WHERE table_name = 'data_invoice_to_cust'
                                 AND table_schema = 'anugscoi_asp'", "AUTO_INCREMENT");
        $this->db->query("ALTER TABLE data_invoice_to_cust AUTO_INCREMENT = ".($ai + 1));

        return $ai;
    }

    function namaPelangganExist($nama_pelanggan) {
        $id_customer = $this->get_scalar("SELECT id_customer FROM master_customer WHERE nama_customer = '".  addslashes($nama_pelanggan)."'", "id_customer");
        return $id_customer;
    }



    function disabledFinalisasi($id) {
        $set = array(
                "is_final" => 1
        );
        $this->db->update("data_invoice_to_cust", $set, "id_invoice = ".$id);
    }
}

?>
